﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using Dapper;
using JetBrains.Annotations;
using System;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.MySql
{
    public class MysqlAutoBusinessDb : AutoBusinessDbServiceBase
    {
        public MysqlAutoBusinessDb(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }

        protected override bool CheckTableExists(string tableName)
        {
            var tableCount = _unitOfWork.Connection.QueryFirst<int>($"SELECT COUNT(1) FROM information_schema.`TABLES` WHERE TABLE_NAME='{_unitOfWork.Connection.Database}' AND TABLE_SCHEMA='{tableName}';");
            return tableCount == 1;
        }

        protected override async Task<bool> DoCreateTable(SpriteObjectDto spriteObjectDto)
        {
            await _unitOfWork.Connection.ExecuteAsync(GetCreateTableSql(spriteObjectDto));
            return true;
        }

        public override async Task<bool> AddObjectProperty(ObjectProperty objectProperty, string tableName)
        {
            StringBuilder sbAddProperty = new StringBuilder();
            sbAddProperty.Append($"ALTER TABLE {MakeNameString(tableName)} ADD COLUMN ");
            sbAddProperty.Append($"{MakeNameString(objectProperty.Name)} {MakeFieldTypeString(objectProperty.FieldType, objectProperty.Length, objectProperty.Length2)}" +
                    $" {MakeFieldIsNull(objectProperty.IsNull)} {MakeFieldDefault(objectProperty.DefaultValue)};");
            await _unitOfWork.Connection.ExecuteAsync(sbAddProperty.ToString());
            return true;
        }

        public override async Task<bool> ModifyObjectProperty(ObjectProperty objectProperty, string tableName)
        {
            StringBuilder sbAddProperty = new StringBuilder();
            sbAddProperty.Append($"ALTER TABLE {MakeNameString(tableName)} MODIFY COLUMN ");
            sbAddProperty.Append($"{MakeNameString(objectProperty.Name)} {MakeFieldTypeString(objectProperty.FieldType, objectProperty.Length, objectProperty.Length2)}" +
                    $" {MakeFieldIsNull(objectProperty.IsNull)} {MakeFieldDefault(objectProperty.DefaultValue)};");
            await _unitOfWork.Connection.ExecuteAsync(sbAddProperty.ToString());
            return true;
        }

        public override async Task<bool> DeleteObjectProperty(string propertyName, string tableName)
        {
            var strSql = $"ALTER TABLE {MakeNameString(tableName)} DROP COLUMN {MakeNameString(propertyName)};";
            await _unitOfWork.Connection.ExecuteAsync(strSql);
            return true;
        }

        #region Private Method

        private string GetCreateTableSql(SpriteObjectDto spriteObjectDto)
        {
            StringBuilder sbCreateTable = new StringBuilder();
            sbCreateTable.Append($"CREATE TABLE {MakeNameString(spriteObjectDto.Name)}(");
            switch (spriteObjectDto.KeyType)
            {
                case EKeyType.Int:
                    sbCreateTable.Append($"{MakeNameString("Id")} INT AUTO_INCREMENT NOT NULL,");
                    break;
                case EKeyType.Guid:
                    sbCreateTable.Append($"{MakeNameString("Id")} CHAR(36) NOT NULL,");
                    break;
            }

            var nameGroups = spriteObjectDto.ObjectPropertyDtos.GroupBy(r => r.Name);
            foreach(var nameGroup in nameGroups)
            {
                if(nameGroup.Count() > 1)
                {
                    throw new SpriteException($"{nameGroup.Key}字段重复");
                }
            }

            foreach (var objectPropertyDto in spriteObjectDto.ObjectPropertyDtos)
            {
                sbCreateTable.Append($"{MakeNameString(objectPropertyDto.Name)} {MakeFieldTypeString(objectPropertyDto.FieldType, objectPropertyDto.Length, objectPropertyDto.Length2)}" +
                    $" {MakeFieldIsNull(objectPropertyDto.IsNull)} {MakeFieldDefault(objectPropertyDto.DefaultValue)},");
            }

            if (spriteObjectDto.IsTree)
            {
                CheckPropertyUnique(spriteObjectDto, "PId");
                switch (spriteObjectDto.KeyType)
                {
                    case EKeyType.Int:
                        sbCreateTable.Append($"{MakeNameString("PId")} INT NOT NULL,");
                        break;
                    case EKeyType.Guid:
                        sbCreateTable.Append($"{MakeNameString("PId")} CHAR(36) NOT NULL,");
                        break;
                }
                CheckPropertyUnique(spriteObjectDto, "TreeCode");
                sbCreateTable.Append($"{MakeNameString("TreeCode")} VARCHAR(767) NULL,");
                CheckPropertyUnique(spriteObjectDto, "Path");
                sbCreateTable.Append($"{MakeNameString("Path")} VARCHAR(1024) NULL,");
                CheckPropertyUnique(spriteObjectDto, "Icon");
                sbCreateTable.Append($"{MakeNameString("Icon")} VARCHAR(200) NULL,");
                CheckPropertyUnique(spriteObjectDto, "Title");
                sbCreateTable.Append($"{MakeNameString("Title")} VARCHAR(200) NULL,");
            }

            if (spriteObjectDto.CreateAudit)
            {
                CheckPropertyUnique(spriteObjectDto, "CreatorId");
                sbCreateTable.Append($"{MakeNameString("CreatorId")} VARCHAR(36) NULL,");
                CheckPropertyUnique(spriteObjectDto, "CreationTime");
                sbCreateTable.Append($"{MakeNameString("CreationTime")} DATETIME(6) NOT NULL,");
            }

            if (spriteObjectDto.ModifyAudit)
            {
                CheckPropertyUnique(spriteObjectDto, "LastModifierId");
                sbCreateTable.Append($"{MakeNameString("LastModifierId")} VARCHAR(36) NULL,");
                CheckPropertyUnique(spriteObjectDto, "LastModificationTime");
                sbCreateTable.Append($"{MakeNameString("LastModificationTime")} DATETIME(6) NOT NULL,");
            }

            if (spriteObjectDto.DeleteAudit)
            {
                CheckPropertyUnique(spriteObjectDto, "DeleterId");
                sbCreateTable.Append($"{MakeNameString("DeleterId")} VARCHAR(36) NULL,");
                CheckPropertyUnique(spriteObjectDto, "DeletionTime");
                sbCreateTable.Append($"{MakeNameString("DeletionTime")} DATETIME(6) NULL,");
                CheckPropertyUnique(spriteObjectDto, "IsDeleted");
                sbCreateTable.Append($"{MakeNameString("IsDeleted")} TINYINT(1) NOT NULL DEFAULT '0',");
            }

            sbCreateTable.Append($"PRIMARY KEY({MysqlConsts.PreMark}Id{MysqlConsts.PostMark})");
            sbCreateTable.Append($") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

            return sbCreateTable.ToString();
        }

        private void CheckPropertyUnique(SpriteObjectDto spriteObjectDto,string fieldName)
        {
            if(spriteObjectDto.ObjectPropertyDtos.Any(r=>r.Name.ToLower() == fieldName.ToLower()))
            {
                throw new SpriteException($"{fieldName}字段重复");
            }
        }

        private string MakeNameString(string name)
        {
            return $"{MysqlConsts.PreMark}{name}{MysqlConsts.PostMark}";
        }

        private string MakeFieldTypeString(EFieldType fieldType, int length, int length2)
        {
            switch (fieldType)
            {
                case EFieldType.String:
                    return $"VARCHAR({length})";
                case EFieldType.Int:
                    return $"INT";
                case EFieldType.Bool:
                    return "TINYINT(1)";
                case EFieldType.Date:
                    return $"DATE";
                case EFieldType.DateTime:
                    return "DATETIME(6)";
                case EFieldType.AutoNumber:
                    return "INT AUTO_INCREMENT";
                case EFieldType.AutoGuid:
                    return "CHAR(36)";
                case EFieldType.Guid:
                    return "CHAR(36)";
                case EFieldType.Text:
                    return "TEXT";
                case EFieldType.Float:
                    return "FLOAT";
                case EFieldType.Decimal:
                    return $"Decimal({length},{length2})";
            }

            return "";
        }

        private string MakeFieldIsNull(bool isNull)
        {
            return isNull ? "NULL" : "NOT NULL";
        }

        private string MakeFieldDefault(string defaultValue)
        {
            if (!string.IsNullOrEmpty(defaultValue))
            {
                return $"DEFAULT '{defaultValue}'";
            }

            return "";
        }

        #endregion
    }
}
